package infoserver;

import java.sql.*;
import java.sql.Driver;

/**
 * Hier wordt de verbinding met de database gemaakt en de sql querys afgehandeld
 * @author Michael Stevens
 * @since 03-05-2012
 */
public class SqlConnection {
    private String url = "jdbc:sqlserver://localhost:49554;",database = "blaat;", username = "test;", password = "test";
    private Connection connection;
    private Statement statement;
    private Infoserver is;
    private ResultSet rs;
    
    
    
    public SqlConnection(Infoserver is){
        this.is = is;
    }
    
    /**
     * Maakt de verbinding met de externe MSSQL database
     * @author Michael Stevens
     * @since 03-05-2011
     */
    public void connect(){
        try{
            try{
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            }catch(Exception e){
                System.out.println(e.getMessage());
            }
            
            connection = DriverManager.getConnection(getUrl() + "databaseName=" + getDatabase() + "user=" + getUsername() + "password=" + getPassword());
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
    }
    
    
    /**
     * Kijkt of de verbinding met de database actief is
     * @return the connection state
     * @author Michael Stevens
     * @since 03-05-2011
     */
    public boolean isConnected(){
        boolean connected = false;
        try{
            if(connection == null){
                connected = false;
            }else if(connection.isValid(0)){
                connected = true;
            }
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
        return connected;
    }
    
    /**
     * Sluit de verbinding en de statement als deze nog actief is.
     * @author Michael Stevens
     * @since 03-05-2011
     */
    public void closeConnection(){
        try{
            if(connection.isValid(0)){
                connection.close();
                statement.close();
            }
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
    }
    
    /**
     * Voer select query uit en geef de resultaten terug
     * @returns results
     * @author Michael Stevens
     * @since 03-05-2011
     */
    public ResultSet excecuteSelectQuery(String query){        
        try{
            rs = statement.executeQuery(query);           
        }catch(Exception e){
            System.out.println(e.getMessage());
        }        
        return rs;        
    }
    
    /**
     * Voer overige query uit en geef terug of het gelukt is of niet
     * @returns results
     * @author Michael Stevens
     * @since 03-05-2011
     */
    public int excecuteUpdateQuery(String query){
        int gelukt = 0;
        try{
            gelukt = statement.executeUpdate(query);
        }catch(Exception e){
            System.out.println(e.getMessage());
            return 0;
        }finally{
            return gelukt;
        }       
        
    }

    /**
     * @return the url
     */
    public String getUrl() {
        return url;
    }

    /**
     * @param url the url to set
     */
    public void setUrl(String url) {
        this.url = url;
    }

    /**
     * @return the database
     */
    public String getDatabase() {
        return database;
    }

    /**
     * @param database the database to set
     */
    public void setDatabase(String database) {
        this.database = database;
    }

    /**
     * @return the username
     */
    public String getUsername() {
        return username;
    }

    /**
     * @param username the username to set
     */
    public void setUsername(String username) {
        this.username = username;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    public void setPassword(String password) {
        this.password = password;
    }
}
